package com.monkey.core.plugin.report.excel;

import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.ConverterFacade;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Excel数据填充器
 */
public class ExcelFiller {
    /**
     * Excel模板数据类型<br>
     * number:数字类型
     */
    public static final String EXCEL_TPL_DATA_TYPE_NUMBER = "number";

    /**
     * Excel模板数据类型<br>
     * number:文本类型
     */
    public static final String EXCEL_TPL_DATA_TYPE_LABEL = "label";

    private static final Logger logger = LoggerFactory.getLogger(ExcelFiller.class);

    private ExcelTemplate excelTemplate = null;

    private ExcelData excelData = null;

    /**
     * 构造函数
     *
     * @param pExcelTemplate
     * @param pExcelData
     */
    public ExcelFiller(ExcelTemplate pExcelTemplate, ExcelData pExcelData) {
        setExcelData(pExcelData);
        setExcelTemplate(pExcelTemplate);

        ConvertUtils.deregister(Date.class);
        DateConverter dateConverter = new DateConverter();
        dateConverter.setPattern("yyyy-MM-dd HH:mm:ss");
        ConvertUtils.register(new ConverterFacade(dateConverter), Date.class);
    }

    /**
     * 数据填充 将ExcelData填入excel模板
     *
     * @return ByteArrayOutputStream
     */
    public void fill() {
        try {
            HSSFWorkbook workbook = this.excelTemplate.getWorkbook();
            HSSFSheet sheet = workbook.getSheetAt(0);
            fillParameters(sheet);
            fillFields(sheet);
        } catch (Exception e) {
            logger.error("基于模板生成可写工作表出错了!");
            e.printStackTrace();
        }
    }

    /**
     * 写入参数对象
     *
     * @param wSheet
     */
    private void fillParameters(HSSFSheet wSheet) {
        List<HSSFCell> parameterCells = getExcelTemplate().getParameterCells();
        Map parameters = getExcelData().getParameters();
        for (HSSFCell cell : parameterCells) {
            String cellContent = cell.getStringCellValue().trim();
            String key = getKey(cellContent);
            String type = getType(cellContent);

            HSSFRow row = wSheet.getRow(cell.getRowIndex());
            row.removeCell(cell);

            HSSFCell hssfCell = row.createCell(cell.getColumnIndex());
            hssfCell.setCellStyle(cell.getCellStyle());

            if (type.equalsIgnoreCase(EXCEL_TPL_DATA_TYPE_NUMBER)) {
                BigDecimal convertValue = (BigDecimal) ConvertUtils.convert(parameters.get(key), BigDecimal.class);
                hssfCell.setCellType(Cell.CELL_TYPE_NUMERIC);
                hssfCell.setCellValue(convertValue.doubleValue());
            } else {
                String convertValue = (String) ConvertUtils.convert(parameters.get(key), String.class);
                hssfCell.setCellType(Cell.CELL_TYPE_STRING);
                hssfCell.setCellValue(convertValue);
            }
        }
    }

    /**
     * 写入表格字段对象
     *
     * @param wSheet
     * @throws Exception
     */
    private void fillFields(HSSFSheet wSheet) throws Exception {
        List<HSSFCell> fieldCells = getExcelTemplate().getFieldCells();
        List fields = getExcelData().getFields();
        for (int j = 0; j < fields.size(); j++) {
            BeanWrapper beanWrapper = new BeanWrapperImpl(fields.get(j));
            HSSFRow row = null;
            for (HSSFCell cell : fieldCells) {
                String cellContent = cell.getStringCellValue().trim();
                String key = getKey(cellContent);
                String type = getType(cellContent);

                if (row == null) {
                    row = wSheet.createRow(cell.getRowIndex() + j);
                }

                HSSFCell hssfCell = row.createCell(cell.getColumnIndex());
                hssfCell.setCellStyle(cell.getCellStyle());
                if (type.equalsIgnoreCase(EXCEL_TPL_DATA_TYPE_NUMBER)) {
                    BigDecimal convertValue = (BigDecimal) ConvertUtils.convert(beanWrapper.getPropertyValue(key), BigDecimal.class);
                    hssfCell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    hssfCell.setCellValue(convertValue.doubleValue());
                } else {
                    String convertValue = (String) ConvertUtils.convert(beanWrapper.getPropertyValue(key), String.class);
                    hssfCell.setCellType(Cell.CELL_TYPE_STRING);
                    hssfCell.setCellValue(convertValue);
                }


            }
        }
        HSSFCell cell = fieldCells.get(0);
        int rowIndex = fields.size() + cell.getRowIndex();
        fillVariables(wSheet, rowIndex);
    }

    /**
     * 写入变量对象
     */
    private void fillVariables(HSSFSheet wSheet, int rowIndex) {
        List<HSSFCell> variableCells = getExcelTemplate().getVariableCells();
        Map variables = getExcelData().getVariables();
        HSSFRow row = wSheet.createRow(rowIndex);
        for (HSSFCell cell : variableCells) {
            String cellContent = cell.getStringCellValue().trim();
            String key = getKey(cellContent);
            String type = getType(cellContent);

            HSSFCell hssfCell = row.createCell(cell.getColumnIndex());
            hssfCell.setCellStyle(cell.getCellStyle());
            if (type.equalsIgnoreCase(EXCEL_TPL_DATA_TYPE_NUMBER)) {
                BigDecimal convertValue = (BigDecimal) ConvertUtils.convert(variables.get(key), BigDecimal.class);
                hssfCell.setCellType(Cell.CELL_TYPE_NUMERIC);
                hssfCell.setCellValue(convertValue.doubleValue());
            } else {
                String content = (String) ConvertUtils.convert(variables.get(key), String.class);
                if (StringUtils.isEmpty(content) && !key.equalsIgnoreCase("nbsp")) {
                    content = key;
                }
                hssfCell.setCellType(Cell.CELL_TYPE_STRING);
                hssfCell.setCellValue(content);
            }
        }
    }


    /**
     * 获取模板键名
     *
     * @param pKey 模板元标记
     * @return 键名
     */
    private static String getKey(String pKey) {
        String key = null;
        int index = pKey.indexOf(":");
        if (index == -1) {
            key = pKey.substring(3, pKey.length() - 1);
        } else {
            key = pKey.substring(3, index);
        }
        return key;
    }

    /**
     * 获取模板单元格标记数据类型
     *
     * @param pType 模板元标记
     * @return 数据类型
     */
    private static String getType(String pType) {
        String type = EXCEL_TPL_DATA_TYPE_LABEL;
        if (pType.contains(":n") || pType.contains(":N")) {
            type = EXCEL_TPL_DATA_TYPE_NUMBER;
        }
        return type;
    }

    public ExcelTemplate getExcelTemplate() {
        return excelTemplate;
    }

    public void setExcelTemplate(ExcelTemplate excelTemplate) {
        this.excelTemplate = excelTemplate;
    }

    public ExcelData getExcelData() {
        return excelData;
    }

    public void setExcelData(ExcelData excelData) {
        this.excelData = excelData;
    }
}
